﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NENAData.Factory;
using NENAData.EF;
using NENAData.Transactions;
using System.Data.SqlClient;

namespace NENAData.Events
{
    public class SlotSession
    {
        private static NENA_MembershipEntities GetConnection()
        {
            var x = new SQLMembership();
            return x.DBConnection();
        }

        private static string GetSQLString()
        {
            var x = new SQLMembership();
            return x.GetMembershipDBString();
        }

        public List<EF.SlotSession> GetSlotId(int intSlotId, bool boolActive)
        {

            NENA_MembershipEntities db = GetConnection();

            //get the transaction object
            //TransactionList transactionList = new TransactionList();
            //Transaction Transaction = transactionList.GetTransaction(transactions);

            //join t in db.Transactions on e.Transaction.TransactionID_NENA equals t.TransactionID_NENA
            //e.Transaction == Transaction
            List<EF.SlotSession> SlotSessionList = (from ss in db.SlotSessions
                                                    where ss.Slot.SlotId == intSlotId
                                                    && ss.Active == boolActive
                                                    select ss).ToList();
            return SlotSessionList;
        }

        public EF.SlotSession GetSession(int intSessionID)
        {

            NENA_MembershipEntities db = GetConnection();

            EF.SlotSession SlotSessionList = (from ss in db.SlotSessions
                                              where ss.SessionId == intSessionID
                                                    select ss).FirstOrDefault();
            return SlotSessionList;
        }

        public List<EF.SlotSession> GetGroup(int intGroupId)
        {

            NENA_MembershipEntities db = GetConnection();

            //get the transaction object
            //TransactionList transactionList = new TransactionList();
            //Transaction Transaction = transactionList.GetTransaction(transactions);

            //join t in db.Transactions on e.Transaction.TransactionID_NENA equals t.TransactionID_NENA
            //e.Transaction == Transaction
            List<EF.SlotSession> SlotSessionList = (from ss in db.SlotSessions
                                                    where ss.Slot.SlotGroup == intGroupId
                                                    select ss).ToList();
            return SlotSessionList;
        }

        public List<EF.SlotSession> GetSessionsByEvent(int intEventId)
        {

            string strSql = @"select SlotSession.SessionId, SlotSession.Speaker, SlotSession.Speaker_URL, 
                            Slot.Time_Start, Slot.Time_End, Slot.Type as 'SlotType', 
                            SlotSession.Description, SlotSession.Description_Long, SlotSession.Description_Notes,
                            SlotSession.Comment_Description, SlotSession.Comment_Enabled, SlotSession.Active
                                from SlotSession
                                    join Slot on SlotSession.SlotId = Slot.SlotId
                                    join SlotGroup on Slot.SlotId = SlotGroup.SlotId
                                    join Events on SlotGroup.EventId = Events.EventId
                                where Events.EventId = @EventId";

            SqlConnection objSqlCon = new SqlConnection(GetSQLString());
            SqlCommand cmd = new SqlCommand(strSql, objSqlCon);
            cmd.Parameters.AddWithValue("@EventId", intEventId);

            objSqlCon.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            //create the return entity
            List<EF.SlotSession> lSlotSession = new List<EF.SlotSession>();

           if (!dr.HasRows)
            {
                return null;
            }

            //NENAData.Extensions.IsNull(dr["Speaker_URL"], "")
            while (dr.Read())
            {

                lSlotSession.Add(new EF.SlotSession
                {
                    SessionId = (int)dr["SessionId"],
                    Speaker = (string)dr["Speaker"],
                    Speaker_URL = NENAData.Extensions.IsNull(dr["Speaker_URL"], ""),
                    Type = (string)dr["SlotType"],
                    Time_Start = (DateTime)dr["Time_Start"],
                    Time_End = (DateTime)dr["Time_End"],
                    Description = (string)dr["Description"],
                    Description_Long = NENAData.Extensions.IsNull(dr["Description_Long"], ""),
                    Description_Notes = NENAData.Extensions.IsNull(dr["Description_Notes"], ""),
                    Comment_Description = NENAData.Extensions.IsNull(dr["Comment_Description"], ""),
                    Comment_Enabled = (bool)dr["Comment_Enabled"],
                    Active = (bool)dr["Active"]
                });

            }
            dr.Close();

            return lSlotSession;
        }

    }
}
